package com.lanqiao.guimei.dao;

import com.lanqiao.guimei.commons.JdbcUtil;
import com.lanqiao.guimei.vo.Page;
import com.lanqiao.guimei.vo.Category;

import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Map;

/**
 * @author StarsOne
 * @date Create in  2019-8-14 0014 09:34:11
 * @description
 */
public class CateGoryDao extends BaseDao {
    Logger logger = Logger.getLogger(CateGoryDao.class);

    /**
     * 增加商品分类
     * @return
     */
    public boolean AddCategory(Category category){
        StringBuffer sql = new StringBuffer("insert into CATEGORY values (cate_seq.nextval,?,?) ");
        List params = new ArrayList();
        try {
            if (category.getCtgrName() != null){
                params.add(category.getCtgrName());
            }else {
                return false;
            }
            if (category.getBigCtgr() != null){
                params.add(category.getBigCtgr());
            }else {
                return false;
            }
            int inserted = runner.update( sql.toString(), params.toArray());
            return(inserted == 1 ? true : false);

        } catch (SQLException e) {
            logger.error("增加商品分类. ", e);
        }

        return false;
    }

    /**
     * 根据ID修改商品分类
     * @return
     */
    public boolean ModifyCategory(Category category){
        StringBuffer sql = new StringBuffer("update CATEGORY set  ");
        List params = new ArrayList();
        try {
            if (category.getCtgrId()==null ){
                return false;
            }
            if (StringUtils.isNoneBlank(category.getCtgrName())){
                sql.append(" CTGR_NAME=? ,");
                params.add(category.getCtgrName());
            }
            if (StringUtils.isNoneBlank(category.getBigCtgr())){
                sql.append(" BIG_CTGR=? ,");
                params.add(category.getBigCtgr());
            }
            //去掉最后一个逗号
            sql.deleteCharAt(sql.lastIndexOf(","));
            sql.append(" where CTGR_ID=?");
            params.add(category.getCtgrId());
            int inserted = runner.update( sql.toString(), params.toArray());
            return(inserted == 1 ? true : false);
        } catch (SQLException e) {
            logger.error("修改商品分类. ", e);
        }

        return false;
    }

    /**
     * 查出大类big_ctgr
     * @return
     */
    public List<Category> Categories(){
        StringBuffer sql = new StringBuffer("select DISTINCT  BIG_CTGR bigCtgr from  CATEGORY where BIG_CTGR=BIG_CTGR");
        try {
            List<Category> categoryList= runner.query(sql.toString(),new BeanListHandler<>(Category.class) );
            if (categoryList!=null){
                return  categoryList;
            }
        } catch (SQLException e) {
            logger.error("查出大类失败. ", e);
        }
        return null;
    }

 /**
     * 根据需要查询的类category.getBigCtgr()统计总记录数
     *
     * @param page
     * @return
     */
    public Integer countForPage(Page<Category> page) {
        Category category = page.getQueryObject();
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer("select count(1) cnt from CATEGORY where 1=1") ;
        if (StringUtils.isNoneBlank(category.getBigCtgr())){
            sql.append(" and BIG_CTGR=? ");
            params.add(category.getBigCtgr());
//            System.out.println(category.getBigCtgr());
        }
        try  (Connection conn = JdbcUtil.getConnection();
              PreparedStatement ps = conn.prepareStatement(sql.toString());
        ) {
            for (int i = 0; i < params.size(); i++) {
                // 设置占位符的值
                ps.setObject(i+1, params.get(i));
            }
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                // 查询结果只有一列，指定列索引
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            logger.error("统计总记录数. ", e);
        }
        return 0;
    }

    /**
     * 分页，按CTGR_ID分类id查询
     * @param page
     * @return
     */
    public List<Category> findForPage(Page<Category> page) {
        Category category = page.getQueryObject();
        StringBuffer sql = new StringBuffer("select * from(" +
                "   select t.*,ROWNUM rn from ("+
                "       select BIG_CTGR bigCtgr, CTGR_NAME ctgrName, CTGR_ID ctgrId  from CATEGORY where 1 =1" );
        List params = new ArrayList();
        try  {
//            if (category.getCtgrId() !=null){
//                sql.append("and CTGR_ID=? ");
//                params.add(category.getCtgrId());}
            if (StringUtils.isNoneBlank(category.getCtgrName())){
                sql.append("and CTGR_NAME=? ");
                params.add(category.getCtgrName());
            } if (StringUtils.isNoneBlank(category.getBigCtgr())){
                sql.append("and BIG_CTGR=? ");
                params.add(category.getBigCtgr());
            }
            sql.append("order by ctgr_id ");
            sql.append( "   ) t where ROWNUM<=?" +
                    ") where rn>=?");
            params.add(page.getLastIndex());
            params.add(page.getFirstIndex());

            return runner.query( sql.toString(),new BeanListHandler<>(Category.class),params.toArray());

        } catch (SQLException e) {
            logger.error("分页查询失败。", e);
        }
        return null;
    }





    /**
     * 查询大分类的分页数据
     *
     * @param page
     * @return
     */
    public List<Category> queryByBigCtgr(Page<Category> page) {
        Category category = page.getQueryObject();
        try {
            return runner.query("select CTGR_ID ctgrId,CTGR_NAME ctgrName,BIG_CTGR bigCtgr from (select * from (select * from category where big_ctgr = ?)  where rownum<=?) where ROWNUM>?", new BeanListHandler<>(Category.class), category.getBigCtgr(), page.getLastIndex(), page.getFirstIndex() - 1);
        } catch (SQLException e) {
            System.err.println("查询商品分类表CateGory总行数失败：" + e.getMessage());
        }
        return null;
    }

    /**
     * 查询大分类下面的小分类数据
     *
     * @return
     */
    public List<Category> queryByBigCtgr(Category category) {
        try {
            return runner.query("select CTGR_ID ctgrId,CTGR_NAME ctgrName,BIG_CTGR bigCtgr from CATEGORY where BIG_CTGR=?", new BeanListHandler<>(Category.class), category.getBigCtgr());
        } catch (SQLException e) {
            System.err.println("查询商品分类表CateGory总行数失败：" + e.getMessage());
        }
        return null;
    }

    public List<Category> queryByBigCtgr(String bigCtor) {
        try {
            return runner.query("select CTGR_ID ctgrId,CTGR_NAME ctgrName,BIG_CTGR bigCtgr from CATEGORY where BIG_CTGR=?", new BeanListHandler<>(Category.class), bigCtor);
        } catch (SQLException e) {
            System.err.println("查询商品分类表CateGory总行数失败：" + e.getMessage());
        }
        return null;
    }


    public Map delete(String CTGR_ID) {
        Map<Object, Object> map = new HashMap<>();
        try {
            if (runner.query("delete  from CATEGORY where CTGR_ID = ？;", new BeanListHandler<>(Category.class), CTGR_ID) !=null){
                map.put("deleted",true);
                return map;
            }
        } catch (SQLException e) {
            System.err.println("删除商品分类表数据失败：" + e.getMessage());
        }
        return null;
    }


    /**
     * 总行数
     *
     * @return
     */
    public int queryAllRow() {
        try {
            return Integer.valueOf(runner.query("select count(1) from category", new ScalarHandler<>()).toString());
        } catch (SQLException e) {
            System.err.println("查询商品分类表CateGory总行数失败：" + e.getMessage());
        }
        return 0;
    }




    /**
     * 删除
     *
     * @return
     */
    public Boolean delete(Integer ctgrId) {
        try {
            int row = runner.update("delete from category where CTGR_ID = ?",ctgrId);
            if (row!=0){
                return true;
            }
        } catch (SQLException e) {
            System.err.println("删除商品分类表C数据失败：" + e.getMessage());
        }
        return false;
    }


}
